﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Web
Imports System.IO

Public Class ds
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents grdDangky As System.Web.UI.WebControls.DataGrid
    Protected WithEvents ddlKhuvuc As System.Web.UI.WebControls.DropDownList
    Protected WithEvents txtTuNgay As System.Web.UI.WebControls.TextBox
    Protected WithEvents txtDenNgay As System.Web.UI.WebControls.TextBox
    Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button
    Protected WithEvents btnExport As System.Web.UI.WebControls.Button

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If Not Page.IsPostBack Then
            If IsNumeric(Request.QueryString("id")) Then
                Dim sSql As String
                sSql = "delete dangkytlco2010 where id=" & Request.QueryString("id")
                Dim myConn As New SqlConnection(ConfigurationManager.AppSettings("cons"))
                Dim myComm As New SqlCommand(sSql, myConn)
                myComm.CommandText = sSql
                myConn.Open()
                myComm.ExecuteNonQuery()
                myConn.Close()
            End If
            BindGrid()
        End If
    End Sub

    Private Sub BindGrid()
        Dim records As Int16 = 10, totalRecords As Int16 = 0
        Dim strWhere As String = " where 1=1 and year(ngaygui)=2012 "
        Dim sTiepthi As String = "N'TP. Hồ Chí Minh', N'Bến Tre', N'Bình Dương', N'Bình Phước', N'Bà Rịa Vũng Tàu', N'Long An', N'Đồng Nai', N'Tây Ninh', N'Tiền Giang', N'Đăk Lăk', N'Đăk Nông', N'Khánh Hoà', N'Bình Thuận', N'Lâm Đồng', N'Ninh Thuận', N'Gia Lai', N'KomTum'"
        Dim sHanoi As String = "N'Hà Nội', N'Bắc Kạn', N'Cao Bằng', N'Hà Giang', N'Hà Tĩnh', N'Hải Phòng', N'Điện Biên', N'Lai Châu', N'Lạng Sơn', N'Lào Cai', N'Nam Định', N'Nghệ An', N'Quảng Ninh', N'Sơn La', N'Thanh Hoá', N'Thái Bình', N'Thái Nguyên', N'Tuyên Quang', N'Yên Bái', N'Bắc Giang', N'Bắc Ninh', N'Hà Nam', N'Hải Dương', N'Hưng Yên', N'Hoà Bình', N'Ninh Bình', N'Phú Thọ', N'Vĩnh Phúc'"
        Dim sDanang As String = "N'Bình Định', N'Huế', N'Đà Nẵng', N'Phú Yên', N'Quảng Bình', N'Quảng Nam', N'Quảng Ngãi', N'Quảng Trị'"
        Dim sCantho As String = "N'An Giang', N'Bạc Liêu', N'Cần Thơ', N'Hậu Giang', N'Cà Mau', N'Kiên Giang', N'Đồng Tháp', N'Sóc Trăng', N'Trà Vinh', N'Vĩnh Long'"

        Select Case ddlKhuvuc.SelectedIndex
            Case 1
                strWhere += " and tinhthanh in (" & sTiepthi & ")"
            Case 2
                strWhere += " and tinhthanh in (" & sHanoi & ")"
            Case 3
                strWhere += " and tinhthanh in (" & sDanang & ")"
            Case 4
                strWhere += " and tinhthanh in (" & sCantho & ")"
        End Select

        If txtTuNgay.Text <> "" And txtDenNgay.Text <> "" Then
            strWhere += "  and datediff(dd, ngaygui, convert(smalldatetime,'" & txtTuNgay.Text & "',103)) <=0 and datediff(dd, ngaygui, convert(smalldatetime,'" & txtDenNgay.Text & "',103))>=0"
        End If

        Dim myConn As New SqlConnection(ConfigurationManager.AppSettings("cons"))
        Dim sSql As String = "select id,hoten,diachiso,duong,phuong,quan,tinhthanh,dienthoai,option1,option2,option3,ghichu,convert(varchar(10),ngaygui,103) as ngayguivn from dangkytlco2010 " & strWhere & " order by ngaygui desc"
        myConn.Open()
        Dim myAdapter As New SqlDataAdapter(sSql, myConn)
        Dim myDataSet As New DataSet
        myAdapter.Fill(myDataSet, sSql)
        totalRecords = myDataSet.Tables(sSql).Rows.Count
        grdDangky.DataSource = myDataSet
        'If totalRecords >= 1.5 * records Then
        '    grdDangky.AllowPaging = True
        '    grdDangky.PageSize = records
        '    grdDangky.PagerStyle.Mode = PagerMode.NumericPages
        '    grdDangky.PagerStyle.Font.Bold = True
        '    grdDangky.PagerStyle.PageButtonCount = 5
        '    grdDangky.PagerStyle.Position = PagerPosition.TopAndBottom
        '    grdDangky.PagerStyle.HorizontalAlign = HorizontalAlign.Right
        'Else
        '    grdDangky.AllowPaging = False
        'End If
        grdDangky.DataBind()
        myConn.Close()
    End Sub

    Public Sub grdDangky_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles grdDangky.PageIndexChanged
        grdDangky.CurrentPageIndex = e.NewPageIndex
        BindGrid()
    End Sub

    Private Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        BindGrid()
    End Sub

    Private Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExport.Click
        ExportToExcel(grdDangky, "ds_dangky.xls")
    End Sub

    Private Shared Sub ExportToExcel(ByVal dataGrd As DataGrid, ByVal fileName As String)
        dataGrd.Columns(10).Visible = False
        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" & fileName)
        HttpContext.Current.Response.Charset = ""
        HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Dim stringWrite As StringWriter = New StringWriter
        Dim htmlWrite As HtmlTextWriter = New HtmlTextWriter(stringWrite)
        dataGrd.RenderControl(htmlWrite)
        HttpContext.Current.Response.Write(stringWrite.ToString())
        HttpContext.Current.Response.End()
    End Sub

End Class
